pip install flask xlwt openpyxl


from flask import Flask, request, jsonify, send_file
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import sqlite3
from datetime import datetime
import io
import os

app = Flask(__name__)

def query_data_by_date_range(start_date, end_date, table_name, db_path, date_column='date_column'):
    """
    根据日期范围从数据库查询数据
    
    Args:
        start_date: 开始日期 (YYYY-MM-DD)
        end_date: 结束日期 (YYYY-MM-DD)
        table_name: 数据库表名
        db_path: 数据库路径
        date_column: 日期字段名
    
    Returns:
        查询结果列表和列名
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    query = f"""
    SELECT * FROM {table_name} 
    WHERE {date_column} BETWEEN ? AND ?
    ORDER BY {date_column}
    """
    
    cursor.execute(query, (start_date, end_date))
    results = cursor.fetchall()
    
    # 获取列名
    column_names = [description[0] for description in cursor.description]
    
    conn.close()
    
    return results, column_names

def create_excel_from_data(data, headers, sheet_name="Sheet1"):
    """
    使用openpyxl将数据写入Excel文件
    
    Args:
        data: 查询结果数据列表
        headers: 表头列表
        sheet_name: 工作表名称
    
    Returns:
        Excel文件的字节流
    """
    # 创建工作簿
    workbook = Workbook()
    worksheet = workbook.active
    worksheet.title = sheet_name
    
    # 设置表头样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")
    
    # 设置边框样式
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # 写入表头
    for col_num, header in enumerate(headers, 1):
        cell = worksheet.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        cell.border = thin_border
    
    # 写入数据
    for row_num, record in enumerate(data, 2):
        for col_num, value in enumerate(record, 1):
            cell = worksheet.cell(row=row_num, column=col_num, value=value if value is not None else "")
            cell.border = thin_border
            
            # 根据数据类型设置对齐方式
            if isinstance(value, (int, float)):
                cell.alignment = Alignment(horizontal="right")
            else:
                cell.alignment = Alignment(horizontal="left", wrap_text=True)
    
    # 自动调整列宽
    for column in worksheet.columns:
        max_length = 0
        column_letter = get_column_letter(column[0].column)
        
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        
        adjusted_width = min(max_length + 2, 50)  # 最大宽度限制为50
        worksheet.column_dimensions[column_letter].width = adjusted_width
    
    # 将工作簿保存到字节流
    output = io.BytesIO()
    workbook.save(output)
    output.seek(0)
    
    return output

@app.route('/export_excel', methods=['POST'])
def export_excel():
    """
    Excel导出接口
    接收开始日期、结束日期、表名等参数，返回Excel文件
    """
    try:
        # 获取请求参数
        data = request.get_json()
        
        start_date = data.get('start_date')
        end_date = data.get('end_date')
        table_name = data.get('table_name', 'default_table')
        db_path = data.get('db_path', 'database.db')
        date_column = data.get('date_column', 'date_column')
        
        # 验证日期格式
        try:
            datetime.strptime(start_date, '%Y-%m-%d')
            datetime.strptime(end_date, '%Y-%m-%d')
        except ValueError:
            return jsonify({'error': '日期格式错误，请使用YYYY-MM-DD格式'}), 400
        
        # 查询数据
        records, headers = query_data_by_date_range(start_date, end_date, table_name, db_path, date_column)
        
        if not records:
            return jsonify({'message': '在指定日期范围内未找到数据'}), 404
        
        # 生成Excel
        excel_stream = create_excel_from_data(records, headers, table_name)
        
        # 返回Excel文件
        filename = f"{table_name}_{start_date}_to_{end_date}.xlsx"
        return send_file(
            excel_stream,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/export_excel_advanced', methods=['POST'])
def export_excel_advanced():
    """
    高级Excel导出接口，支持更多自定义选项
    """
    try:
        # 获取请求参数
        data = request.get_json()
        
        start_date = data.get('start_date')
        end_date = data.get('end_date')
        table_name = data.get('table_name', 'default_table')
        db_path = data.get('db_path', 'database.db')
        date_column = data.get('date_column', 'date_column')
        custom_headers = data.get('custom_headers', None)  # 自定义表头
        include_summary = data.get('include_summary', False)  # 是否包含汇总信息
        
        # 验证日期格式
        try:
            datetime.strptime(start_date, '%Y-%m-%d')
            datetime.strptime(end_date, '%Y-%m-%d')
        except ValueError:
            return jsonify({'error': '日期格式错误，请使用YYYY-MM-DD格式'}), 400
        
        # 查询数据
        records, original_headers = query_data_by_date_range(start_date, end_date, table_name, db_path, date_column)
        
        if not records:
            return jsonify({'message': '在指定日期范围内未找到数据'}), 404
        
        # 使用自定义表头（如果提供）
        headers = custom_headers if custom_headers else original_headers
        
        # 创建工作簿
        workbook = Workbook()
        worksheet = workbook.active
        worksheet.title = table_name
        
        # 设置样式
        header_font = Font(bold=True, color="FFFFFF", size=12)
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 写入表头
        for col_num, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col_num, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border
        
        # 写入数据
        for row_num, record in enumerate(records, 2):
            for col_num, value in enumerate(record, 1):
                cell = worksheet.cell(row=row_num, column=col_num, value=value if value is not None else "")
                cell.border = thin_border
                
                # 根据数据类型设置对齐方式和格式
                if isinstance(value, (int, float)):
                    cell.alignment = Alignment(horizontal="right")
                else:
                    cell.alignment = Alignment(horizontal="left", wrap_text=True)
        
        # 如果需要汇总信息
        if include_summary and records:
            last_row = len(records) + 1
            worksheet.merge_cells(f'A{last_row}:G{last_row}')
            summary_cell = worksheet.cell(row=last_row, column=1, value=f'总计: {len(records)} 条记录')
            summary_cell.font = Font(bold=True, color="FF0000")
            summary_cell.alignment = Alignment(horizontal="center")
            last_row += 1
            worksheet.cell(row=last_row, column=1, value="导出时间:")
            worksheet.cell(row=last_row, column=2, value=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        
        # 自动调整列宽
        for column in worksheet.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)
            
            for cell in column:
                try:
                    if cell.value and len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            
            adjusted_width = min(max_length + 2, 50)
            worksheet.column_dimensions[column_letter].width = adjusted_width
        
        # 将工作簿保存到字节流
        output = io.BytesIO()
        workbook.save(output)
        output.seek(0)
        
        # 返回Excel文件
        filename = f"{table_name}_{start_date}_to_{end_date}_advanced.xlsx"
        return send_file(
            excel_stream,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 创建示例数据库和表用于测试
def init_test_database():
    """初始化测试数据库"""
    conn = sqlite3.connect('test_database.db')
    cursor = conn.cursor()
    
    # 创建测试表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS sales_data (
            id INTEGER PRIMARY KEY,
            date_column TEXT,
            product_name TEXT,
            quantity INTEGER,
            price REAL,
            total REAL,
            sales_person TEXT
        )
    ''')
    
    # 插入测试数据
    test_data = [
        ('2023-01-15', '产品A', 10, 25.50, 255.00, '张三'),
        ('2023-01-16', '产品B', 5, 40.00, 200.00, '李四'),
        ('2023-01-17', '产品C', 8, 30.00, 240.00, '王五'),
        ('2023-02-01', '产品A', 12, 25.50, 306.00, '张三'),
        ('2023-02-02', '产品D', 3, 55.00, 165.00, '赵六'),
        ('2023-02-03', '产品B', 7, 40.00, 280.00, '李四'),
        ('2023-03-10', '产品E', 15, 20.00, 300.00, '王五'),
        ('2023-03-15', '产品A', 6, 25.50, 153.00, '张三'),
    ]
    
    cursor.executemany(
        'INSERT OR REPLACE INTO sales_data (date_column, product_name, quantity, price, total, sales_person) VALUES (?, ?, ?, ?, ?, ?)',
        test_data
    )
    
    conn.commit()
    conn.close()

@app.route('/test_export', methods=['GET'])
def test_export():
    """测试导出功能的示例页面"""
    html_content = '''
    <!DOCTYPE html>
    <html>
    <head>
        <title>Excel导出测试 (openpyxl)</title>
        <style>
            body { 
                font-family: Arial, sans-serif; 
                margin: 40px; 
                background-color: #f5f5f5;
            }
            .container {
                max-width: 800px;
                margin: 0 auto;
                background: white;
                padding: 30px;
                border-radius: 8px;
                box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            }
            h2 { 
                color: #333; 
                text-align: center;
                margin-bottom: 30px;
            }
            .form-group { 
                margin: 15px 0; 
                display: flex;
                align-items: center;
            }
            label { 
                display: inline-block; 
                width: 120px; 
                font-weight: bold;
                color: #555;
            }
            input, select { 
                padding: 10px; 
                margin: 5px; 
                border: 1px solid #ddd;
                border-radius: 4px;
                flex: 1;
            }
            button { 
                padding: 12px 25px; 
                background: #007bff; 
                color: white; 
                border: none; 
                border-radius: 4px;
                cursor: pointer; 
                font-size: 16px;
                margin: 10px 5px;
            }
            button:hover { 
                background: #0056b3; 
            }
            .btn-secondary {
                background: #6c757d;
            }
            .btn-secondary:hover {
                background: #545b62;
            }
            .section {
                margin-bottom: 30px;
                padding: 20px;
                border: 1px solid #e9ecef;
                border-radius: 4px;
            }
            .section-title {
                font-size: 18px;
                font-weight: bold;
                margin-bottom: 15px;
                color: #333;
            }
        </style>
    </head>
    <body>
        <div class="container">
            <h2>Excel导出测试 (openpyxl)</h2>
            
            <div class="section">
                <div class="section-title">基础导出</div>
                <form id="basicExportForm">
                    <div class="form-group">
                        <label>开始日期:</label>
                        <input type="date" id="startDate" name="start_date" value="2023-01-01" required>
                    </div>
                    <div class="form-group">
                        <label>结束日期:</label>
                        <input type="date" id="endDate" name="end_date" value="2023-12-31" required>
                    </div>
                    <div class="form-group">
                        <label>表名:</label>
                        <select id="tableName" name="table_name">
                            <option value="sales_data">销售数据</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>数据库:</label>
                        <select id="dbPath" name="db_path">
                            <option value="test_database.db">测试数据库</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>日期字段:</label>
                        <input type="text" id="dateColumn" name="date_column" value="date_column" required>
                    </div>
                    <button type="submit">导出Excel (基础)</button>
                </form>
            </div>
            
            <div class="section">
                <div class="section-title">高级导出</div>
                <form id="advancedExportForm">
                    <div class="form-group">
                        <label>开始日期:</label>
                        <input type="date" name="start_date" value="2023-01-01" required>
                    </div>
                    <div class="form-group">
                        <label>结束日期:</label>
                        <input type="date" name="end_date" value="2023-12-31" required>
                    </div>
                    <div class="form-group">
                        <label>包含汇总:</label>
                        <input type="checkbox" name="include_summary" value="true" style="width: auto;">
                    </div>
                    <button type="submit">导出Excel (高级)</button>
                </form>
            </div>
        </div>
        
        <script>
            // 基础导出
            document.getElementById('basicExportForm').addEventListener('submit', async function(e) {
                e.preventDefault();
                
                const formData = new FormData(this);
                const jsonData = {};
                for (let [key, value] of formData.entries()) {
                    // 将复选框值转换为布尔值
                    jsonData[key] = key === 'include_summary' ? value === 'true' : value;
                }
                
                try {
                    const response = await fetch('/export_excel', {
                        method: 'POST',
                        headers: {
                            'Content-Type': 'application/json',
                        },
                        body: JSON.stringify(jsonData)
                    });
                    
                    if (response.ok) {
                        const blob = await response.blob();
                        const url = window.URL.createObjectURL(blob);
                        const a = document.createElement('a');
                        a.href = url;
                        a.download = response.headers.get('Content-Disposition')?.split('filename=')[1] || 'export.xlsx';
                        a.click();
                        window.URL.revokeObjectURL(url);
                    } else {
                        const error = await response.json();
                        alert('导出失败: ' + error.error);
                    }
                } catch (error) {
                    alert('请求失败: ' + error.message);
                }
            });
            
            // 高级导出
            document.getElementById('advancedExportForm').addEventListener('submit', async function(e) {
                e.preventDefault();
                
                const formData = new FormData(this);
                const jsonData = {};
                for (let [key, value] of formData.entries()) {
                    jsonData[key] = key === 'include_summary' ? value === 'true' : value;
                }
                
                jsonData.table_name = 'sales_data';
                jsonData.db_path = 'test_database.db';
                jsonData.date_column = 'date_column';
                
                try {
                    const response = await fetch('/export_excel_advanced', {
                        method: 'POST',
                        headers: {
                            'Content-Type': 'application/json',
                        },
                        body: JSON.stringify(jsonData)
                    });
                    
                    if (response.ok) {
                        const blob = await response.blob();
                        const url = window.URL.createObjectURL(blob);
                        const a = document.createElement('a');
                        a.href = url;
                        a.download = response.headers.get('Content-Disposition')?.split('filename=')[1] || 'advanced_export.xlsx';
                        a.click();
                        window.URL.revokeObjectURL(url);
                    } else {
                        const error = await response.json();
                        alert('导出失败: ' + error.error);
                    }
                } catch (error) {
                    alert('请求失败: ' + error.message);
                }
            });
        </script>
    </body>
    </html>
    '''
    return html_content

if __name__ == '__main__':
    # 初始化测试数据库
    init_test_database()
    app.run(debug=True)